I designed two mongoDB collections, one called accounts and the other called emails. I am trying to get the email address from 'accounts' in 'emails', for Sender, and Sent_To.
accounts:
{"_id":"5dc1d4b273adfdeff818d2d0","email_Addr":"[email protected]"},
{"_id":"5dc335d8c2169ee670b8a80b","email_Addr":"[email protected]"},
{"_id":"5dc33492c2169ee670b8a80a","email_Addr":"[email protected]"},
{"_id":"5dc335d8c2169ee670b8a80e","email_Addr":"[email protected]"},
{"_id":"5dcac904fe0dcf9fc0183ee2","email_Addr":"[email protected]"}
emails:
{"_id":"5dcacb41fe0dcf9fc0183ee3","Sender":"5dc1d4b273adfdeff818d2d0","Subject":"[HR-888] Notice of official announcement","Sent_To":[{"receiver":"5dc335d8c2169ee670b8a80b"}]},
{"_id":"5dcacd6efe0dcf9fc0183ee4","Sender":"5dc33492c2169ee670b8a80a","Subject":"Happy New Year! Greetings for the New Year.","Sent_To":[{"Receiver":"5dc335d8c2169ee670b8a80e"},{"Receiver":"5dcac904fe0dcf9fc0183ee2"}]}
Using the following aggregate I managed to have it working for Sender, but when aggregating for Sent_To it only returns if there are more than one element in the array.
db.emails.aggregate([
{
$lookup:
{
from: "accounts",
localField: "Sender",
foreignField: "_id",
as: "SenderId"
}
},
{
$unwind: "$SenderId"
},
{ $lookup:
{
from: "accounts",
localField: "Sent_To.Receiver",
foreignField: "_id",
as: "SentID"
}
},
{
$project:{
_id: 1,
Sender: "$SenderId.email_Addr",
Subject: 1,
Sent_To: "$SentID.email_Addr",
}
}
]);
Result:
{ "_id" : ObjectId("5dcacb41fe0dcf9fc0183ee3"), "Subject" : "[HR-888] Notice of official announcement", "Sender" : "[email protected]", "Sent_To" : [ ] } //Should be Sent_To [ "[email protected]" ]
{ "_id" : ObjectId("5dcacd6efe0dcf9fc0183ee4"), "Subject" : "Happy New Year! Greetings for the New Year.", "Sender" : "[email protected]", "Sent_To" : [ "[email protected]", "[email protected]" ] }